﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LPBToolsLib
{
    /// <summary>
    /// 以数据库方式控制Excel表格文件
    /// 注意：
    /// 1、由于是采用的数据表方式，第一行必须是表示的字段名；
    /// 2、写入的数据列，必须与字段信息相匹配；
    /// 3、Excel2007以前（不包括）的文件和Excel2007以后（包括）的文件是采用不同的数据库连接字；
    /// 4、该方式控制的Excel文件，不可删除记录。
    /// </summary>
    public class OleExcelDB
    {
        protected string sXLSFileName = "";
        public string XLSFileName { get { return sXLSFileName; } }

        public bool IsReady
        {
            get
            {
                return (!string.IsNullOrEmpty(sXLSFileName))
                && (!string.IsNullOrEmpty(Path.GetExtension(sXLSFileName)));
            }
        }
        /// <summary>
        /// 数据库连接字
        /// Excel文件在服務器上的OLE連接字符串
        /// HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
        /// 当 IMEX = 0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
        /// 当 IMEX = 1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
        /// 当 IMEX = 2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
        /// </summary>
        /// <param name="no_HDR"></param>
        /// <returns></returns>
        protected string ExcelConnectStr(bool no_HDR)
        {
            if (IsReady)
            {
                string sConnStrMask12 = "Provider=Microsoft.Ace.OleDb.12.0;data source={0};Extended Properties='Excel 12.0; {1} IMEX=2'"; //此连接可以操作.xls与.xlsx文件
                string sConnStrMask04 = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source={0};Extended Properties='Excel 8.0; {1} IMEX=2'"; //此连接只能操作Excel2007之前(.xls)文件
                string sHDR = no_HDR ? "HDR=NO;" : "HDR=YES;";

                if (Path.GetExtension(sXLSFileName).Equals(".xlsx"))
                {
                    return string.Format(sConnStrMask12, sXLSFileName, sHDR);
                }
                else
                {
                    return string.Format(sConnStrMask04, sXLSFileName, sHDR);
                }
            }
            return "";
        }

        public OleExcelDB(string sNewXLSFile)
        {
            if ((!string.IsNullOrEmpty(sNewXLSFile))
                && (!string.IsNullOrEmpty(Path.GetExtension(sNewXLSFile))))
            {
                sXLSFileName = sNewXLSFile;
            }
        }
        /// <summary>
        /// 根据提供的字段列表创表
        /// 如果xls文件不存在，则直接创建
        /// </summary>
        /// <param name="sNewTBName">表名称，不包括'$'</param>
        /// <param name="sFieldList">字段列表</param>
        /// <returns></returns>
        public bool CreateXLSFile(string sNewTBName, string[] sFieldList)
        {
            if ((!IsReady)
                || File.Exists(sXLSFileName)
                || string.IsNullOrWhiteSpace(sNewTBName)
                || (sFieldList == null))
                return false;
            else
            {
                try
                {
                    using (OleDbConnection conn = new OleDbConnection(ExcelConnectStr(false)))
                    {
                        conn.Open();
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.Connection = conn;
                        if (sNewTBName.LastIndexOf('$') > 0)
                        {
                            sNewTBName = sNewTBName.Substring(sNewTBName.Length - 1);
                        }
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 3600;
                        StringBuilder sql = new StringBuilder();
                        sql.Append("CREATE TABLE [" + sNewTBName + "](");
                        foreach (string s in sFieldList)
                        {
                            sql.Append("[" + s + "] text,");
                        }
                        sql = sql.Remove(sql.Length - 1, 1);
                        sql.Append(")");
                        cmd.CommandText = sql.ToString();
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
            }
            return File.Exists(sXLSFileName);
        }
        /// <summary>
        /// 根据Excel物理路径获取Excel文件中所有表名,列名是TABLE_NAME
        /// </summary>
        /// <returns>TableNameList</returns>
        public List<string> GetExcelSheetNamesList()
        {
            List<string> sResult = new List<string>();
            DataTable dt = null;
            if (IsReady && File.Exists(sXLSFileName))
            {
                using (OleDbConnection conn = new OleDbConnection(ExcelConnectStr(false)))
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    }
                    catch (Exception ee)
                    {
                        throw new Exception(ee.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
                if (dt != null)
                    foreach (DataRow row in dt.Rows)
                        sResult.Add(row["TABLE_NAME"].ToString());
            }
            return sResult;
        }
        /// <summary>
        /// 将数据写入到XLS文间中
        /// </summary>
        /// <param name="sFieldList">字段名列表</param>
        /// <param name="tRows">记录列表，其列信息必须与字段对应，否则不会写入</param>
        /// <param name="sDestSheetName">目标表名称，不包含'$'</param>
        /// <returns>返回写入的记录数量，-1表示操作错误</returns>
        public int Datas2Sheet(string[] sFieldList, List<List<string>> tRows, string sDestSheetName)
        {
            int iResult = -1;
            if (IsReady
                && (!string.IsNullOrEmpty(sDestSheetName))
                && (sFieldList != null)
                && (sFieldList.Length > 0)
                && (tRows != null)
                && (tRows.Count > 0))
            {
                iResult = 0;
                StringBuilder strSQL = new StringBuilder();
                //看看目標表是否已存在
                List<string> tables = GetExcelSheetNamesList();
                if (!tables.Contains(sDestSheetName + "$"))
                {
                    CreateXLSFile(sDestSheetName, sFieldList);
                }
                //存在,直接寫入
                using (OleDbConnection conn = new OleDbConnection(ExcelConnectStr(false)))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    foreach (List<string> tRow in tRows)
                    {
                        if ((tRow != null) && (tRow.Count == sFieldList.Length))
                        {
                            string strfield = "";
                            string strvalue = "";
                            for (int i = 0; i < tRow.Count; i++)
                            {
                                strfield += "[" + sFieldList[i] + "],";
                                strvalue += "'" + tRow[i] + "',";
                            }
                            strfield = strfield.TrimEnd(',');
                            strvalue = strvalue.TrimEnd(',');
                            cmd.CommandText = string.Format(" insert into [{0}]({1})values({2})",
                                 sDestSheetName + "$",
                                 strfield,
                                 strvalue);
                        }
                        iResult += cmd.ExecuteNonQuery();
                    }
                }
            }
            return iResult;
        }

    }

    /// <summary>
    /// 注意：
    /// 1、XLSX文件需要安装对应的AccessDatabaseEngine，才能够访问；
    /// 2、读取数据时，建议使用ExcelConnectRead连接字符串，保证数据读取正确；
    /// </summary>
    public class OleExcelDBV2
    {
        protected string sXLSFileName = "";
        public string XLSFileName { get { return sXLSFileName; } }

        public bool IsReady
        {
            get
            {
                return (!string.IsNullOrEmpty(sXLSFileName))
                && (!string.IsNullOrEmpty(Path.GetExtension(sXLSFileName)));
            }
        }

        protected string ExcelConnectStr(bool no_HDR)
        {
            return ExcelConnectStr(FileAccess.ReadWrite, no_HDR);
        }
        /// <summary>
        /// 数据库连接字
        /// Excel文件在服務器上的OLE連接字符串
        /// HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
        /// 当 IMEX = 0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
        /// 当 IMEX = 1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
        /// 当 IMEX = 2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
        /// 注意：
        /// Microsoft.Ace.OleDb.12.0需要安装对应的AccessDatabaseEngine，才能够访问；
        /// </summary>
        /// <param name="tAccess">文件访问权限</param>
        /// <param name="no_HDR">第一行是否是标题</param>
        /// <returns>返回连接字符串</returns>
        protected string ExcelConnectStr(FileAccess tAccess, bool no_HDR)
        {
            if (IsReady)
            {
                int iIMEX = 2;
                switch (tAccess)
                {
                    case FileAccess.Read:
                        iIMEX = 1;
                        break;
                    case FileAccess.Write:
                        iIMEX = 0;
                        break;
                }

                string sConnStrMask12 = "Provider=Microsoft.Ace.OleDb.12.0;data source={0};Extended Properties='Excel 12.0; {1} IMEX=" + iIMEX.ToString() + "'"; //此连接可以操作.xls与.xlsx文件
                string sConnStrMask04 = "Provider=Microsoft.Jet.OleDb.4.0;data source={0};Extended Properties='Excel 8.0; {1} IMEX=" + iIMEX.ToString() + "'"; //此连接只能操作Excel2007之前(.xls)文件
                string sHDR = no_HDR ? "HDR=NO;" : "HDR=YES;";

                if (Path.GetExtension(sXLSFileName).Equals(".xlsx"))
                {
                    return string.Format(sConnStrMask12, sXLSFileName, sHDR);
                }
                else
                {
                    return string.Format(sConnStrMask04, sXLSFileName, sHDR);
                }
            }
            return "";
        }

        public string ExcelConnectRead(bool no_HDR)
        {
            return ExcelConnectStr(FileAccess.Read, no_HDR);
        }
        public string ExcelConnectWrite(bool no_HDR)
        {
            return ExcelConnectStr(FileAccess.Write, no_HDR);
        }



        public OleExcelDBV2(string sNewXLSFile)
        {
            if ((!string.IsNullOrEmpty(sNewXLSFile))
                && (!string.IsNullOrEmpty(Path.GetExtension(sNewXLSFile))))
            {
                sXLSFileName = sNewXLSFile;
            }
        }
        /// <summary>
        /// 根据提供的字段列表创表
        /// 如果xls文件不存在，则直接创建
        /// </summary>
        /// <param name="sNewTBName">表名称，不包括'$'</param>
        /// <param name="sFieldList">字段列表</param>
        /// <returns></returns>
        public bool CreateXLSFile(string sNewTBName, string[] sFieldList)
        {
            if ((!IsReady)
                || File.Exists(sXLSFileName)
                || string.IsNullOrWhiteSpace(sNewTBName)
                || (sFieldList == null))
                return false;
            else
            {
                try
                {
                    using (OleDbConnection conn = new OleDbConnection(ExcelConnectStr(false)))
                    {
                        conn.Open();
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.Connection = conn;
                        if (sNewTBName.LastIndexOf('$') > 0)
                        {
                            sNewTBName = sNewTBName.Substring(sNewTBName.Length - 1);
                        }
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 3600;
                        StringBuilder sql = new StringBuilder();
                        sql.Append("CREATE TABLE [" + sNewTBName + "](");
                        foreach (string s in sFieldList)
                        {
                            sql.Append("[" + s + "] text,");
                        }
                        sql = sql.Remove(sql.Length - 1, 1);
                        sql.Append(")");
                        cmd.CommandText = sql.ToString();
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ee)
                {
                    throw ee;
                }
            }
            return File.Exists(sXLSFileName);
        }
        /// <summary>
        /// 根据Excel物理路径获取Excel文件中所有表名,列名是TABLE_NAME
        /// </summary>
        /// <returns>TableNameList</returns>
        public List<string> GetExcelSheetNamesList()
        {
            List<string> sResult = new List<string>();
            DataTable dt = null;
            if (IsReady && File.Exists(sXLSFileName))
            {
                using (OleDbConnection conn = new OleDbConnection(ExcelConnectStr(false)))
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    }
                    catch (Exception ee)
                    {
                        throw new Exception(ee.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
                if (dt != null)
                    foreach (DataRow row in dt.Rows)
                        sResult.Add(row["TABLE_NAME"].ToString());
            }
            return sResult;
        }
        /// <summary>
        /// 将数据写入到XLS文间中
        /// </summary>
        /// <param name="sFieldList">字段名列表</param>
        /// <param name="tRows">记录列表，其列信息必须与字段对应，否则不会写入</param>
        /// <param name="sDestSheetName">目标表名称，不包含'$'</param>
        /// <returns>返回写入的记录数量，-1表示操作错误</returns>
        public int Datas2Sheet(string[] sFieldList, List<List<string>> tRows, string sDestSheetName)
        {
            int iResult = -1;
            if (IsReady
                && (!string.IsNullOrEmpty(sDestSheetName))
                && (sFieldList != null)
                && (sFieldList.Length > 0)
                && (tRows != null)
                && (tRows.Count > 0))
            {
                iResult = 0;
                StringBuilder strSQL = new StringBuilder();
                //看看目標表是否已存在
                List<string> tables = GetExcelSheetNamesList();
                if (!tables.Contains(sDestSheetName + "$"))
                {
                    CreateXLSFile(sDestSheetName, sFieldList);
                }
                //存在,直接寫入
                using (OleDbConnection conn = new OleDbConnection(ExcelConnectStr(false)))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    foreach (List<string> tRow in tRows)
                    {
                        if ((tRow != null) && (tRow.Count == sFieldList.Length))
                        {
                            string strfield = "";
                            string strvalue = "";
                            for (int i = 0; i < tRow.Count; i++)
                            {
                                strfield += "[" + sFieldList[i] + "],";
                                strvalue += "'" + tRow[i] + "',";
                            }
                            strfield = strfield.TrimEnd(',');
                            strvalue = strvalue.TrimEnd(',');
                            cmd.CommandText = string.Format(" insert into [{0}]({1})values({2})",
                                 sDestSheetName + "$",
                                 strfield,
                                 strvalue);
                        }
                        iResult += cmd.ExecuteNonQuery();
                    }
                }
            }
            return iResult;
        }
    }
}
